package com.softer.chengxin.report.dao;

import com.softer.chengxin.report.dao.provider.ReportPrintDaoProvider;
import com.softer.chengxin.report.entity.Report;
import com.softer.chengxin.score.entity.ScoreCompositeProvince;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author Gong Zhiyang
 * @date 2020/1/18
 */
@Mapper
@Repository
public interface ReportPrintDao {
    /**
     * 查询信用报告列表
     *
     * @param scoreCompositeProvince 查询实体
     * @return 列表
     */
    @SelectProvider(type = ReportPrintDaoProvider.class, method = "getReportList")
    List<ScoreCompositeProvince> getReportList(@Param("s") ScoreCompositeProvince scoreCompositeProvince);
    /**
     * 查询信用报告
     *
     * @param id 信用ID
     * @return 信用报告
     */
    @Select("SELECT p.id,p.`level`, b.name as qyName,b.business_license_no as businessLicenseNo,b.legal_person as legalPerson,d.`name` as registerType,IFNULL(aa.num,0) as excellentNum,IFNULL(bb.num,0) as negativeNum," +
            "IFNULL(p.excellent_score,0) as excellentScore,IFNULL(p.negative_score,0) as negativeScore,IFNULL(cc.num,0) as projectNum,IFNULL(cc.area,0) as allArea,IFNULL(p.all_score,0) as allScore,IFNULL(dd.num,0) as personNum,IFNULL(f.num,0) as manageNum" +
            " FROM score_composite_province p LEFT JOIN company_base_info b ON b.id =p.company_id LEFT JOIN dic_info d ON d.code = b.register_type" +
            " LEFT JOIN (SELECT corp_id,COUNT(1) as num FROM score_detail WHERE info_type=1 GROUP BY corp_id )aa ON aa.corp_id=p.company_id" +
            " LEFT JOIN (SELECT corp_id,COUNT(1) as num FROM score_detail WHERE info_type=2 GROUP BY corp_id )bb ON bb.corp_id=p.company_id" +
            " LEFT JOIN (SELECT COUNT(1) as num,SUM(with_elevator_area) as area,company_id FROM company_project GROUP BY company_id)cc ON cc.company_id=p.company_id" +
            " LEFT JOIN (SELECT company_id,COUNT(1) as num FROM company_person WHERE grade_certificate=1 GROUP BY company_id) f ON f.company_id=p.company_id"+
            " LEFT JOIN (SELECT company_id,COUNT(1) as num FROM company_person GROUP BY company_id)dd ON dd.company_id=p.company_id WHERE p.id=#{id}")
    Report getReportById(@Param("id") Integer id);
}
